﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;

namespace TOOL
{
    class TExportExcel
    {
        #region 物流中心
        /// <summary>
        /// ExportExcel（使用NPOI的方式）
        /// </summary>
        /// <param name="DT"></param>
        /// <param name="applicationPath">程序所在位置</param>
        /// <param name="fileName">文件路径（包含文件名）</param>
        /// <param name="FN">文件名</param>
        public static string Export(DataTable DT, string applicationPath, string fileName, string FN)
        {
            string resultMessage = string.Empty;
            try
            {
                HSSFWorkbook hssfworkbookDown;
                string modelExlPath = applicationPath + "\\idcard.xls";
                if (File.Exists(modelExlPath) == false)//模板不存在
                {
                    resultMessage = "EXCEL模板不存在，请联系系统管理员";
                }
                using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbookDown = new HSSFWorkbook(file);
                    file.Close();
                }
                if (DT.Rows.Count > 0)
                {
                    WriterExcel(hssfworkbookDown, 0, DT, FN);

                    FileStream files = new FileStream(fileName, FileMode.Create);
                    hssfworkbookDown.Write(files);
                    files.Close();
                    if (File.Exists(fileName) == false)//附件生成失败
                    {
                        resultMessage = "导出失败！请重新导出或者联系系统管理员！";
                    }
                    else
                    {
                        resultMessage = "导出成功！";
                    }
                }
            }
            catch (Exception ex)
            {
                resultMessage = ex.Message;
            }
            return resultMessage;
        }
        /// <summary>
        /// WriterExcel
        /// </summary>
        /// <param name="hssfworkbookDown"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="DT"></param>
        /// <param name="FN">文件名，用于写入标题行</param>
        private static void WriterExcel(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT, string FN)
        {
            try
            {
                #region 设置单元格样式
                /*
                //字体
                HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont();
                fontS9.FontName = "Arial";
                fontS9.FontHeightInPoints = 10;
                fontS9.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.NORMAL;
                //表格
                ICellStyle TableS9 = (ICellStyle)hssfworkbookDown.CreateCellStyle();
                TableS9.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                TableS9.WrapText = true;
                TableS9.SetFont(fontS9);
                 * */
                //标题样式
                //字体样式
                //单元格样式
                HSSFFont titleFont = (HSSFFont)hssfworkbookDown.CreateFont();
                titleFont.FontName = "宋体";
                titleFont.FontHeightInPoints = 18;
                titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

                ICellStyle titleStyle = (ICellStyle)hssfworkbookDown.CreateCellStyle();
                titleStyle.SetFont(titleFont);
                titleStyle.WrapText = true;
                titleStyle.Alignment = HorizontalAlignment.CENTER;
                titleStyle.VerticalAlignment = VerticalAlignment.CENTER;

                ICellStyle cellStyle = (ICellStyle)hssfworkbookDown.CreateCellStyle();
                cellStyle.WrapText = true;
                cellStyle.Alignment = HorizontalAlignment.LEFT;
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                ICellStyle cellStyle2 = (ICellStyle)hssfworkbookDown.CreateCellStyle();
                cellStyle2.Alignment = HorizontalAlignment.CENTER;
                cellStyle2.VerticalAlignment = VerticalAlignment.CENTER;
                #endregion

                HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
                hssfworkbookDown.SetSheetHidden(sheetIndex, false);
                hssfworkbookDown.SetActiveSheet(sheetIndex);

                #region 定义excel行
                //标题行  CreateRow是从0开始的
                HSSFRow dataRow1 = (HSSFRow)sheet.CreateRow(0);
                dataRow1.CreateCell(0);
                dataRow1.Cells[0].SetCellValue(FN);
                dataRow1.Cells[0].CellStyle = titleStyle;
                for (int i = 0; i < DT.Rows.Count; i++)//循环创建单元格
                {
                    HSSFRow _hssfRow = (HSSFRow)sheet.CreateRow(i + 2);
                    _hssfRow.HeightInPoints=100;
                    //首列序号
                    _hssfRow.CreateCell(0);
                    _hssfRow.Cells[0].SetCellValue(i + 1);
                    //其他数据
                    for (int j = 0; j < DT.Columns.Count; j++)
                    {//循环创建单元格
                        _hssfRow.CreateCell(j+1);
                    }                     
                    _hssfRow.Cells[1].SetCellValue(DT.Rows[i]["到访时间"].ToString());
                    _hssfRow.Cells[2].SetCellValue(DT.Rows[i]["姓名"].ToString());
                    _hssfRow.Cells[3].SetCellValue(DT.Rows[i]["年龄"].ToString());
                    _hssfRow.Cells[4].SetCellValue(DT.Rows[i]["民族"].ToString());
                    _hssfRow.Cells[5].SetCellValue(DT.Rows[i]["性别"].ToString());
                    _hssfRow.Cells[6].SetCellValue(DT.Rows[i]["出生日期"].ToString());
                    _hssfRow.Cells[7].SetCellValue(DT.Rows[i]["身份证号"].ToString());
                    _hssfRow.Cells[8].SetCellValue(DT.Rows[i]["手机号"].ToString());
                    _hssfRow.Cells[9].SetCellValue(DT.Rows[i]["体温"].ToString());
                    _hssfRow.Cells[10].SetCellValue(DT.Rows[i]["地址"].ToString());  
                    _hssfRow.Cells[11].SetCellValue(DT.Rows[i]["签发机关"].ToString());
                    _hssfRow.Cells[12].SetCellValue(DT.Rows[i]["有效期限"].ToString());
                    _hssfRow.Cells[13].SetCellValue(DT.Rows[i]["照片"].ToString());
                    //设置到访时间、地址等过长的宽度
                    sheet.SetColumnWidth(1, 20 * 256);
                    sheet.SetColumnWidth(4, 5 * 256);
                    sheet.SetColumnWidth(5, 5 * 256);
                    sheet.SetColumnWidth(6, 10 * 256);
                    sheet.SetColumnWidth(7, 19 * 256);
                    sheet.SetColumnWidth(8, 12 * 256);
                    sheet.SetColumnWidth(9, 5 * 256);
                    sheet.SetColumnWidth(10, 13 * 256);                    
                    sheet.SetColumnWidth(12, 12 * 256);
                    sheet.SetColumnWidth(11, 10 * 256);
                    _hssfRow.Cells[0].CellStyle = cellStyle2;
                    _hssfRow.Cells[2].CellStyle = cellStyle2;
                    _hssfRow.Cells[3].CellStyle = cellStyle2;
                    _hssfRow.Cells[5].CellStyle = cellStyle2;
                    _hssfRow.Cells[9].CellStyle = cellStyle2;
                    _hssfRow.Cells[10].CellStyle = cellStyle;
                    _hssfRow.Cells[11].CellStyle = cellStyle;
                    _hssfRow.Cells[12].CellStyle = cellStyle;
                    //插入身份证照片

                    string _photoName = string.Format("{0}\\photo\\{1}", Application.StartupPath, DT.Rows[i]["照片"].ToString());
                    byte[] _photoBytes = File.ReadAllBytes(_photoName);
                    if (!string.IsNullOrEmpty(_photoName))
                    {
                        int photoIdx = hssfworkbookDown.AddPicture(_photoBytes, PictureType.JPEG);
                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                        int col = 13;
                        int row = i + 2;
                        _hssfRow.CreateCell(col);
                        sheet.SetColumnWidth(col, 13 * 256);
                        ////##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，
                        ///右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为126
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, col, row, col, row);
                        HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, photoIdx);
                        _photoBytes = null;
                    }

                }
                //设置标题
                
                #endregion 
                //设定第一行，第一列的单元格选中
                sheet.SetActiveCell(0, 0);
            }
            catch (Exception ex)
            {
                TLog.WriteFail(string.Format("*************{0}导出数据错误：{1}",DateTime.Now,ex.Message));
            }
        }
        #endregion
    }
}
